CREATE EXTERNAL TABLE jms_dwd.dwd_yl_oms_oms_order_hf( 
  `id` bigint COMMENT '订单编号',                      
  `customer_order_id` string COMMENT '客户订单编号',     
  `order_source_code` string COMMENT '订单来源code',   
  `order_source_name` string COMMENT '订单来源名称',     
  `order_type_code` bigint COMMENT '订单类型code',     
  `waybill_id` string COMMENT '运单号',               
  `origin_id` bigint COMMENT '始发地id',              
  `sender_name` string COMMENT '寄件人姓名',            
  `sender_province_id` bigint COMMENT '寄件省份id',    
  `sender_city_id` bigint COMMENT '寄件城市id',        
  `sender_area_id` bigint COMMENT '寄件区域Id',        
  `sender_township` string COMMENT '寄件乡镇',         
  `sender_street` string COMMENT '寄件街道',           
  `sender_detailed_address` string COMMENT '寄件详细地址',  
  `receiver_name` string COMMENT '收件人姓名',          
  `destination_id` bigint COMMENT '目的地id',         
  `receiver_province_id` bigint COMMENT '收件省份id',  
  `receiver_city_id` bigint COMMENT '收件城市id',      
  `receiver_area_id` bigint COMMENT '收件区域id',      
  `receiver_township` string COMMENT '收件乡镇',       
  `receiver_street` string COMMENT '收件街道',         
  `receiver_detailed_address` string COMMENT '收件详细地址',  
  `terminal_dispatch_code` string COMMENT '三段码',   
  `first_code` string COMMENT '一段码',               
  `second_code` string COMMENT '二段码',              
  `third_code` string COMMENT '三段码',               
  `express_type_code` string COMMENT '快件类型code',   
  `goods_type_code` string COMMENT '物品类型code',     
  `goods_name` string COMMENT '物品名称',              
  `packate_volume` decimal(9,2) COMMENT '包裹体积重,单位立方厘米',  
  `total_freight` decimal(14,2) COMMENT '总运费',     
  `customer_code` string COMMENT '客户编号code',       
  `customer_name` string COMMENT '客户编号名称',         
  `customer_order_time` timestamp COMMENT '客户下单时间',  
  `input_time` timestamp COMMENT '订单录入时间',         
  `order_status_code` bigint COMMENT '订单状态code',   
  `cancel_time` timestamp COMMENT '订单取消时间',        
  `real_pick_network_code` string COMMENT '实际取件网点code',  
  `dispatch_network_time` timestamp COMMENT '调度网点时间',  
  `pick_network_code` string COMMENT '取件网点code',   
  `pick_time` timestamp COMMENT '取件时间',            
  `assigner_name` string COMMENT '委托人姓名',          
  `update_time` timestamp COMMENT '更新时间',          
  `is_delete` smallint COMMENT '是否删除,1未删除，2已删除',   
  `is_plaintext` tinyint COMMENT '',               
  `create_by_code` string COMMENT '创建人编码',         
  `create_by_name` string COMMENT '创建人姓名',         
  `settlement_weight` decimal(14,2) COMMENT '结算重量',  
  `proxy_area_code` string COMMENT '代理区code',      
  `proxy_area_name` string COMMENT '代理区name',      
  `real_name` string COMMENT '实名姓名',               
  `customer_waybill_no` string COMMENT '客户运单号',    
  `dispatch_network_code` string COMMENT '派件网点code',
  `goods_type_name` string COMMENT '物品类型名称',
  `sign_receipt` tinyint COMMENT '签回单 0:否 ，1:是，2:回单',  
  `is_transfer` tinyint COMMENT '是否转寄(1否 2是)')     
COMMENT '订单去重记录表'
PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
STORED AS PARQUET
LOCATION '/dw/hive/jms_dwd.db/external/dwd_yl_oms_oms_order_hf'
TBLPROPERTIES (
'discover.partitions' = 'false',
'parquet.column.index.access' = 'true'
);

alter table jms_dwd.dwd_yl_oms_oms_order_hf add columns (
    sender_province_name string comment'发件省份'
    ,sender_city_name string comment'发件城市'
    ,sender_area_name string comment'发件区域'
    ,receiver_province_name string comment'派件省份'
    ,receiver_city_name string comment'派件城市'
    ,receiver_area_name string comment'派件区域'
) cascade;

alter table jms_dwd.dwd_yl_oms_oms_order_hf add columns (
    subscribe_source_name string comment '订单订阅来源名称'
) cascade;
	alter table jms_dwd.dwd_yl_oms_oms_order_hf add columns
(member_id  bigint   comment'会员id',
sender_mobile_phone   string    comment'寄件人手机号',
receiver_mobile_phone    string   comment'收件人手机号'
) cascade;

	alter table jms_dwd.dwd_yl_oms_oms_order_hf add columns
(receiver_encrypt_phone string comment '上一次调度网点code改为收件人加密手机号'
) cascade;

alter table jms_dwd.dwd_yl_oms_oms_order_hf  add columns (
dispatch_staff_time    timestamp  comment'调度业务员时间',
back_dispatch_network_time    timestamp comment'打回调度订单时间'
)cascade;